import pandas as pd
import os

# 获取目录及子目录下所有excel文件绝对路径
excel_files = [os.path.join(root, f) for root, dirs, files in os.walk('E:\\小象第二批\\zhungeer2.7z\\准格尔金诚2\\4.长银消金-3699笔-齐\\长银-放款流水、代偿证明、债转协议-齐\\长银-放款流水\\长银-放款流水-1942笔') for f in files if f.endswith('.xlsx')]
for excel_file in excel_files:
    file_path = excel_file
    print(file_path)
    # 根据表格“序号”补齐缺失的行，并合并所有sheet页
    # 最终输出到一个新的Excel文件

    # 读取Excel文件，sheet_name=None表示读取所有sheet页
    excel_file = pd.ExcelFile(excel_file)

    # 遍历Excel文件中的每个sheet页
    for sheet_name in excel_file.sheet_names:
        # 读取当前sheet页的数据
        sheet_data = excel_file.parse(sheet_name)

        # 确保'序号'列存在，如果不存在则添加一列全为NaN
        if '序号' not in sheet_data.columns:
            sheet_data['序号'] = pd.np.nan

        # 将'序号'列转换为整数类型，非数字的数据将被转换为NaN
        sheet_data['序号'] = pd.to_numeric(sheet_data['序号'], errors='coerce')

        # 找到缺失的行号范围
        max_serial_number = sheet_data['序号'].max()
        if pd.isna(max_serial_number):
            max_serial_number = 0
        missing_numbers = set(range(1, int(max_serial_number) + 1)) - set(sheet_data['序号'].dropna())

        # 如果存在缺失的行号
        if missing_numbers:
            # 创建一个新的DataFrame用于存储缺失的行，并确保'序号'列是正确的类型
            missing_data = pd.DataFrame(list(missing_numbers), columns=['序号'])
            missing_data['序号'] = missing_data['序号'].astype(int)

            # 将缺失的行添加到原数据的底部，并按照'序号'列排序
            sheet_data = pd.concat([sheet_data, missing_data]).sort_values(by='序号').reset_index(drop=True)

        # 删除'序号'列中的重复值（如果需要）
        sheet_data = sheet_data.drop_duplicates(subset='序号')

        # 修改路径中获取的文件名，在文件名后增加"-new"，文件格式不变
        new_file_name = file_path.replace('.xlsx', '-new.xlsx')

        # 将补全后的数据输出到一个新的Excel文件，文件名使用原始sheet页名称
        output_file_name = new_file_name
        sheet_data.to_excel(output_file_name, index=False)

        print(f"已输出补全后的数据到 {output_file_name}")